﻿Public Class AccesoADatos
    Dim conexion As New Data.OleDb.OleDbConnection
    Dim cmd As New Data.OleDb.OleDbCommand
    Dim string_conexion As String

    Public Property _StrConexion() As String
        Get
            Return Me.string_conexion
        End Get
        Set(ByVal value As String)
            Me.string_conexion = value
        End Set
    End Property

    Public Sub New()
        string_conexion = "Provider=SQLOLEDB;Data Source=GTºN1\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=C:\DB\LIBRERIA_MAGOO.MDF"
        Me.conexion.ConnectionString = string_conexion
    End Sub

    Public Sub New(ByVal strConexion As String)
        string_conexion = strConexion
        Me.conexion.ConnectionString = Me.string_conexion
    End Sub

    Private Sub conectar()
        Try
            If conexion.State <> ConnectionState.Open Then
                conexion.Open()
            End If
            Me.cmd.Connection = conexion
            Me.cmd.CommandType = CommandType.Text
        Catch ex As OleDb.OleDbException
            conexion.Close()
            Throw ex
        End Try
    End Sub

    Private Function query(ByVal comando As String) As Data.DataTable
        Dim tabla As New Data.DataTable
        Me.conectar()
        Me.cmd.CommandText = comando
        tabla.Load(Me.cmd.ExecuteReader())
        Me.conexion.Close()
        Return tabla
    End Function

    Private Sub nonQuery(ByVal comando As String)
        Me.conectar()
        Me.cmd.CommandText = comando
        Me.cmd.ExecuteNonQuery()
        Me.conexion.Close()
    End Sub

    Public Function leerTabla(ByVal nombreTabla As String) As Data.DataTable
        Dim comando As String = "SELECT * FROM " & nombreTabla
        Return Me.query(comando)
    End Function

    Public Function leerTabla(ByVal columnas As String, ByVal nombreTabla As String) As Data.DataTable
        Dim comando As String = "SELECT " & columnas & " FROM " & nombreTabla
        Return Me.query(comando)
    End Function

    Public Function leerTabla(ByVal columnas As String, ByVal nombreTabla As String, ByVal condicion As String) As Data.DataTable
        Dim comando As String = "SELECT " & columnas & " FROM " & nombreTabla & " WHERE " & condicion & " "
        Return Me.query(comando)
    End Function

    Public Sub borrarRegistro(ByVal nombreTabla As String, ByVal nombrePk As String, ByVal valorPk As Double)
        Dim comando As String = "DELETE FROM " & nombreTabla & " WHERE " & nombrePk & " = " & valorPk
        Me.nonQuery(comando)
    End Sub

    Public Sub borrarRegistro(ByVal nombreTabla As String, ByVal nombrePk As String, ByVal valorPk As String)
        Dim comando As String = "DELETE FROM " & nombreTabla & " WHERE " & nombrePk & " = '" & valorPk & "'"
        Me.nonQuery(comando)
    End Sub

    Public Sub borrarRegistro(ByVal nombreTabla As String, ByVal nombrePk1 As String, ByVal valorPk1 As Integer, ByVal nombrePk2 As String, ByVal valorPk2 As Integer)
        Dim comando As String = "DELETE FROM " & nombreTabla & " WHERE " & nombrePk1 & " = " & valorPk1 & " AND " & nombrePk2 & " = " & valorPk2
        Me.nonQuery(comando)
    End Sub

    Public Sub insertarRegistro(ByVal nombreTabla As String, ByVal valores As String)
        Dim comando As String = "INSERT INTO " & nombreTabla & " VALUES (" & valores & ")"
        Me.nonQuery(comando)
    End Sub

    Public Sub modificarRegistro(ByVal nombreTabla As String, ByVal valores As String, ByVal nombrePk As String, ByVal valorPk As Integer)
        Dim comando As String = "UPDATE " & nombreTabla & " SET " & valores & " WHERE " & nombrePk & " = " & valorPk
        Me.nonQuery(comando)
    End Sub

    Public Sub modificarRegistro(ByVal nombreTabla As String, ByVal valores As String, ByVal nombrePk As String, ByVal valorPk As String)
        Dim comando As String = "UPDATE " & nombreTabla & " SET " & valores & " WHERE " & nombrePk & " = '" & valorPk & "'"
        Me.nonQuery(comando)
    End Sub

    Public Sub modificarRegistro(ByVal nombreTabla As String, ByVal valores As String, ByVal nombrePk1 As String, ByVal valorPk1 As String, ByVal nombrePk2 As String, ByVal valorPk2 As String)
        Dim comando As String = "UPDATE " & nombreTabla & " SET " & valores & " WHERE " & nombrePk1 & " = '" & valorPk1 & "'"
        Me.nonQuery(comando)
    End Sub
End Class
